﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;

namespace OracleCompare.Core
{
    public class OracleHelper
    {
        private string connString;

        public OracleHelper(string UID, string UserID, string Password)
        {
            connString = "Data Source=" + UID + ";User ID=" + UserID + ";Password=" + Password + ";";
        }

        public void TestConfig()
        {
            using (OracleConnection conn = new OracleConnection(connString))
            {
                conn.Open();
            }
        }

        public DataTable GetDataTable(string sql)
        {
            using (OracleConnection conn = new OracleConnection(connString))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand(sql, conn);
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }

        public DataTable GetDataTable(string sql, OracleParameter[] ops)
        {
            using (OracleConnection conn = new OracleConnection(connString))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand(sql, conn);

                foreach (OracleParameter op in ops)
                {
                    cmd.Parameters.Add(op);
                }

                OracleDataAdapter da = new OracleDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }

        /// <summary>
        /// 获取Oracle表结构
        /// </summary>
        /// <returns></returns>
        public List<TableEntity> GetOracleTables(string condition, string uncondition)
        {
            //string sql = "select table_name,comments from user_tab_comments where table_type='TABLE'";
            string sql = BuildTableSql(condition, uncondition);
            DataTable dt = GetDataTable(sql);

            List<TableEntity> tables = new List<TableEntity>();
            foreach (DataRow dr in dt.Rows)
            {
                TableEntity table = new TableEntity();
                table.TableName = (dr["table_name"] == null || dr["table_name"] == DBNull.Value) ? "" : dr["table_name"].ToString();
                table.Comments = (dr["comments"] == null || dr["comments"] == DBNull.Value) ? "" : dr["comments"].ToString();
                tables.Add(table);
            }

            return tables;
        }

        private string BuildTableSql(string condition, string uncondition)
        {
            string sql = "select table_name,comments from user_tab_comments where table_type='TABLE'";

            if (!string.IsNullOrEmpty(condition))
            {
                sql = sql + " and (";
                string[] strs = condition.Split(',');
                for (int i = 0; i < strs.Length; i++)
                {
                    if (i == 0)
                    {
                        sql = " " + sql + " table_name like  '" + strs[i] + "'";
                    }
                    else
                    {
                        sql = " " + sql + " or table_name like  '" + strs[i] + "'";
                    }
                }

                sql = sql + ") ";
            }

            if (!string.IsNullOrEmpty(uncondition))
            {
                sql = sql + " and (";
                string[] strs = uncondition.Split(',');
                for (int i = 0; i < strs.Length; i++)
                {
                    if (i == 0)
                    {
                        sql = " " + sql + " table_name not like  '" + strs[i] + "'";
                    }
                    else
                    {
                        sql = " " + sql + " and table_name not like  '" + strs[i] + "'";
                    }
                }

                sql = sql + ") ";
            }

            return sql;
        }

        /// <summary>
        /// 获取Oracle所有视图
        /// </summary>
        /// <returns></returns>
        public List<ViewEntity> GetOracleViews(string condition, string uncondition)
        {
            //string sql = "select view_name,text from user_views";
            string sql = BuildViewSql(condition, uncondition);
            DataTable dt = GetDataTable(sql);

            List<ViewEntity> views = new List<ViewEntity>();

            foreach (DataRow dr in dt.Rows)
            {
                ViewEntity view = new ViewEntity();
                view.ViewName = (dr["view_name"] == null || dr["view_name"] == DBNull.Value) ? "" : dr["view_name"].ToString();
                view.Text = (dr["text"] == null || dr["text"] == DBNull.Value) ? "" : dr["text"].ToString();
                views.Add(view);
            }

            return views;
        }

        private string BuildViewSql(string condition, string uncondition)
        {
            string sql = "select view_name,text from user_views where 1=1 ";

            if (!string.IsNullOrEmpty(condition))
            {
                sql = sql + " and (";
                string[] strs = condition.Split(',');
                for (int i = 0; i < strs.Length; i++)
                {
                    if (i == 0)
                    {
                        sql = " " + sql + " view_name like  '" + strs[i] + "'";
                    }
                    else
                    {
                        sql = " " + sql + " or view_name like  '" + strs[i] + "'";
                    }
                }

                sql = sql + ") ";
            }

            if (!string.IsNullOrEmpty(uncondition))
            {
                sql = sql + " and (";
                string[] strs = uncondition.Split(',');
                for (int i = 0; i < strs.Length; i++)
                {
                    if (i == 0)
                    {
                        sql = " " + sql + " view_name not like  '" + strs[i] + "'";
                    }
                    else
                    {
                        sql = " " + sql + " and view_name not like  '" + strs[i] + "'";
                    }
                }

                sql = sql + ") ";
            }

            return sql;
        }

        /// <summary>
        /// 获取Oracle所有列
        /// </summary>
        /// <returns></returns>
        public List<ColumnEntity> GetOracleColumns()
        {
            string sql = @"
select user_tab_columns.table_name,
       user_tab_columns.column_name,
       user_tab_columns.data_type,
       decode(decode(user_tab_columns.data_type,
                     'NUMBER',
                     null,
                     'DATE',
                     null,
                     'CLOB',
                     null,
                     'BLOB',
                     null,
                     'LONG',
                     null,
                     user_tab_columns.data_length),
              0,
              null) data_length,
       user_tab_columns.nullable,
       user_tab_columns.data_default,
       user_col_comments.comments
  from user_tab_columns, user_tab_comments, user_col_comments
 where user_tab_columns.TABLE_NAME = user_tab_comments.TABLE_NAME
   and user_tab_columns.COLUMN_NAME = user_col_comments.column_name
   and user_tab_columns.TABLE_NAME = user_col_comments.table_name
   and user_tab_comments.table_type = 'TABLE'";
            DataTable dt = GetDataTable(sql);

            List<ColumnEntity> columns = new List<ColumnEntity>();

            foreach (DataRow dr in dt.Rows)
            {
                ColumnEntity column = new ColumnEntity();
                column.TableName = (dr["table_name"] == null || dr["table_name"] == DBNull.Value) ? "" : dr["table_name"].ToString();
                column.ColumnName = (dr["column_name"] == null || dr["column_name"] == DBNull.Value) ? "" : dr["column_name"].ToString();
                column.DataType = (dr["data_type"] == null || dr["data_type"] == DBNull.Value) ? "" : dr["data_type"].ToString();
                column.DataLength = (dr["data_length"] == null || dr["data_length"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["data_length"].ToString());

                if (dr["nullable"] == null || dr["nullable"] == DBNull.Value)
                {
                    column.Nullable = true;
                }
                else
                {
                    if (dr["nullable"].ToString() == "TRUE")
                    {
                        column.Nullable = true;
                    }
                    if (dr["nullable"].ToString() == "FALSE")
                    {
                        column.Nullable = false;
                    }
                }
                column.DataDefault = (dr["data_default"] == null || dr["data_default"] == DBNull.Value) ? "" : dr["data_default"].ToString();
                column.Comments = (dr["comments"] == null || dr["comments"] == DBNull.Value) ? "" : dr["comments"].ToString();

                columns.Add(column);
            }

            return columns;
        }
        /// <summary>
        /// 获取Oracle所有函数、存储过程、类型、包头、包体、Job
        /// </summary>
        /// <returns></returns>
        public List<ProcedureEntity> GetOracleProcedures(string condition,
            string uncondition, bool IsCompareContent)
        {
            //string sql = "select distinct type, name from user_source order by type,name";
            string sql = BuildProcedureSql(condition, uncondition);
            DataTable dt = GetDataTable(sql);
            DataTable dtSource = new DataTable();
            if (IsCompareContent)
            {
                dtSource = GetOracleSource(condition, uncondition);
            }

            List<ProcedureEntity> procedures = new List<ProcedureEntity>();


            foreach (DataRow dr in dt.Rows)
            {
                ProcedureEntity procedure = new ProcedureEntity();
                procedure.Name = (dr["name"] == null || dr["name"] == DBNull.Value) ? "" : dr["name"].ToString();
                procedure.Type = (dr["type"] == null || dr["type"] == DBNull.Value) ? "" : dr["type"].ToString();

                if (IsCompareContent)
                {
                    StringBuilder sb = new StringBuilder();
                    //foreach (DataRow drSource in dtSource.Select("name='" + procedure.Name + "' and type='" + procedure.Type + "' order by line"))
                    foreach (DataRow drSource in dtSource.Select("name='" + procedure.Name + "' and type='" + procedure.Type + "'"))
                    {
                        sb.AppendLine((drSource["text"] == null || drSource["text"] == DBNull.Value) ? "" : drSource["text"].ToString());
                    }

                    procedure.Text = sb.ToString();
                }
                procedures.Add(procedure);
            }

            return procedures;
        }

        private string BuildProcedureSql(string condition, string uncondition)
        {
            string sql = "select distinct type, name from user_source where 1=1 ";

            if (!string.IsNullOrEmpty(condition))
            {
                sql = sql + " and (";
                string[] strs = condition.Split(',');
                for (int i = 0; i < strs.Length; i++)
                {
                    if (i == 0)
                    {
                        sql = " " + sql + " name like  '" + strs[i] + "'";
                    }
                    else
                    {
                        sql = " " + sql + " or name like  '" + strs[i] + "'";
                    }
                }

                sql = sql + ") ";
            }

            if (!string.IsNullOrEmpty(uncondition))
            {
                sql = sql + " and (";
                string[] strs = uncondition.Split(',');
                for (int i = 0; i < strs.Length; i++)
                {
                    if (i == 0)
                    {
                        sql = " " + sql + " name not like '" + strs[i] + "'";
                    }
                    else
                    {
                        sql = " " + sql + " and name not like '" + strs[i] + "'";
                    }
                }

                sql = sql + ") ";
            }

            sql = sql + " order by type,name";

            return sql;
        }

        private DataTable GetOracleSource(string condition, string uncondition)
        {
            //string sql = "select name, type, line, text from user_source order by type, name, line";
            string sql = BuildSourceSql(condition, uncondition);
            return GetDataTable(sql);
        }

        private string BuildSourceSql(string condition, string uncondition)
        {
            string sql = "select name, type, line, text from user_source where 1=1 ";

            if (!string.IsNullOrEmpty(condition))
            {
                sql = sql + " and (";
                string[] strs = condition.Split(',');
                for (int i = 0; i < strs.Length; i++)
                {
                    if (i == 0)
                    {
                        sql = " " + sql + " name like  '" + strs[i] + "'";
                    }
                    else
                    {
                        sql = " " + sql + " or name like  '" + strs[i] + "'";
                    }
                }

                sql = sql + ") ";
            }

            if (!string.IsNullOrEmpty(uncondition))
            {
                sql = sql + " and (";
                string[] strs = uncondition.Split(',');
                for (int i = 0; i < strs.Length; i++)
                {
                    if (i == 0)
                    {
                        sql = " " + sql + " name not like  '" + strs[i] + "'";
                    }
                    else
                    {
                        sql = " " + sql + " and name not like  '" + strs[i] + "'";
                    }
                }

                sql = sql + ") ";
            }

            sql = sql + " order by type, name, line";

            return sql;
        }

    }
}
